﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace DAL
{
    public class BaseDataClass
    {
        /// <summary/>
        private string connectionString;

        /// <summary/>
        /// 

        public string ConnectionString
        {
            get
            {
                string str = ConfigurationManager.ConnectionStrings["NalfeDemoConnectionString"].ConnectionString;
                if (str == null || str.Length <= 0)
                    throw (new ApplicationException("Connection String configuration is missing from config. "));
                else
                    return (str);
            }
            set
            {
                connectionString = value;
            }
        }

        /// <summary>
        /// The GenerateCollectionFromReader delegate represents any method
        /// which returns a collection from a SQL Data Reader.
        /// </summary>
        public delegate CollectionBase GenerateCollectionFromReader(SqlDataReader returnData);

        /// <summary>
        /// The GenerateObjectFromReader delegate represents any method
        /// which returns a object from a SQL Data Reader.
        /// </summary>
        public delegate Object GenerateObjectFromReader(SqlDataReader returnData);

        /// <summary>
        /// Adds a parameter to a SqlCommand
        /// </summary>
        /// <param name="sqlCmd">SqlCommand</param>
        /// <param name="paramId"></param>
        /// <param name="sqlType"></param>
        /// <param name="paramSize"></param>
        /// <param name="paramDirection"></param>
        /// <param name="paramvalue"></param>
        public void AddParamToSQLCmd(SqlCommand sqlCmd, string paramId, SqlDbType sqlType, int paramSize, ParameterDirection paramDirection, object paramvalue)
        {
            // Validate Parameter Properties
            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));
            if (paramId == string.Empty)
                throw (new ArgumentOutOfRangeException("paramId"));

            // Add Parameter
            SqlParameter newSqlParam = new SqlParameter();
            newSqlParam.ParameterName = paramId;
            newSqlParam.SqlDbType = sqlType;
            newSqlParam.Direction = paramDirection;

            if (paramSize > 0)
                newSqlParam.Size = paramSize;

            if (paramvalue != null)
                newSqlParam.Value = paramvalue;

            sqlCmd.Parameters.Add(newSqlParam);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public Object ExecuteObjectReader(SqlCommand sqlCmd, GenerateObjectFromReader gofr)
        {
            // Validate Command Properties
            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            //The C# using block guarantees that the Dispose method is called o
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sqlCmd.Connection = cn;
                cn.Open();
                Object temp = gofr(sqlCmd.ExecuteReader());
                return (temp);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public Object ExecuteCollectionReader(SqlCommand sqlCmd, GenerateCollectionFromReader gcfr)
        {
            // Validate Command Properties
            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            //The C# using block guarantees that the Dispose method is called o
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sqlCmd.Connection = cn;
                cn.Open();
                Object temp = gcfr(sqlCmd.ExecuteReader());
                return (temp);
            }
        }

        /// <summary>
        /// Executes the query, and returns the first column of the first row in the 
        /// result set returned by the query. Extra columns or rows are ignored.
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns></returns>
        public Object ExecuteScalarCmd(SqlCommand sqlCmd)
        {
            // Validate Command Properties
            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            Object result = null;

            //The C# using block guarantees that the Dispose method is called 
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sqlCmd.Connection = cn;
                cn.Open();
                result = sqlCmd.ExecuteScalar();
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <param name="gcfr"></param>
        /// <returns></returns>
        public CollectionBase ExecuteReaderCmd(SqlCommand sqlCmd, GenerateCollectionFromReader gcfr)
        {
            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            //The C# using block guarantees that the Dispose method is called
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sqlCmd.Connection = cn;
                cn.Open();
                CollectionBase temp = gcfr(sqlCmd.ExecuteReader());
                return (temp);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        public void SetCommandType(SqlCommand sqlCmd, CommandType cmdType, string cmdText)
        {
            sqlCmd.CommandType = cmdType;
            sqlCmd.CommandText = cmdText;
        }

        //protected IList<T> ExecuteListObjectReader<T>(SqlCommand sqlCmd) where T : new()
        //{
        //    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
        //    {
        //        sqlCmd.Connection = cn;
        //        cn.Open();
        //        var reader = sqlCmd.ExecuteReader();
        //        var mapper = new DataReaderMapper<T>();
        //        var result = mapper.MapListAll(reader); // 3. 
        //        return result;
        //    }
        //}

        //protected T ExecuteObjectReader<T>(SqlCommand sqlCmd) where T : new()
        //{
        //    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
        //    {
        //        sqlCmd.Connection = cn;
        //        cn.Open();
        //        var reader = sqlCmd.ExecuteReader();
        //        var mapper = new DataReaderMapper<T>();
        //        var result = mapper.MapListAll(reader).FirstOrDefault(); // 3. 
        //        return result;
        //    }
        //}

        //khai báo connection sql
        public static SqlConnection Connection;
        public BaseDataClass()
        {
            if (Connection == null)
            {
                Connection = new SqlConnection(ConnectionString);
            }
        }
        //open connection
        public SqlConnection GetConnection()
        {
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
                return Connection;
            }
            else
            {
                return Connection;
            }
        }
        
        //close connection  
        public SqlConnection CloseConnection()
        {
            if (Connection.State == ConnectionState.Open)
            {
                Connection.Close();
                return Connection;
            }
            else
            {
                return Connection;
            }
        }
    }
}
